ITL Waits

Oracle INITRANS tips Eliminating data block contention involves eliminating ?hot? data blocks from the application, and super-high concurrent DML can cause block contention that will be relieved by increasing INITRANS for the effected tables and indexes. Since each transaction updating a block requires a transaction entry, you can also increase the INITRANS value for high volume databases. Use a tool (like Ion) to see the specific table of index that has the contention, that's a clue. Also see this article to tell you how to find the specific table or index, and consider increasing INITRANS for that object. All about INITRANS The INITRANS setting controls Initial Transaction Slots (ITLs). A transaction slot is required for any session that needs to modify a block in an object. For tables INITRANS defaults to 1 for indexes, 2. The MAXTRANS setting controls the maximum number of ITLs that a block can allocate (usually defaults to 255). If a block is sparsely populated then Oracle will dynamically increase the number of ITLs up to MAXTRANS. ITL's, INITRANS and Block Waits However, if the block has little or no free space then transactions will serialize waiting on a free ITL. This is one cause for data base block waits. By setting INITRANS to the number of expected simultaneous DML (data manipulation language - insert, update and delete) transaction for a single block, you can avoid serialization for ITL slots. The maximum value suggested for INITRANS is 100 and settings over this size rarely improve performance. Therefore a setting of INITRANS to the average number of simultaneous DML users and setting MAXTRANS to 100 will most likely result in the best utilization of resources and performance. Remember, each ITL requires approximately 23 bytes in the block header. The v$segment_statistics view provides a statistic_name and value column for each table, unfortunately this format doesn't lend itself to easy use. By utilizing the crosstab technique we can easily create a report to show us these vital tuning statistics for our system. An example of this type of cross tab report is shown below. col "Object" format a20 set numwidth 12 set lines 132 set pages 50 @title132 'Object Wait Statistics' spool rep_out\&&db\obj_stat_xtab select * from ( select DECODE (GROUPING(a.object_name), 1, 'All Objects', a.object_name) AS "Object", sum(case when a.statistic_name = 'ITL waits' then a.value else null end) "ITL Waits", sum(case when a.statistic_name = 'buffer busy waits' then a.value else null end) "Buffer Busy Waits", sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits", sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads", sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads" from v$segment_statistics a / spool off clear columns ttitle off The cross tab report generates a listing showing the statistics of concern as headers across the page rather than listings going down the page and summarizes them by object. This allows us to easily compare total buffer busy waits to the number of ITL or row lock waits. This ability to compare the ITL and row lock waits to buffer busy waits lets us see what objects may be experiencing contention for ITL lists, which may be experiencing excessive locking activity and through comparisons, which are highly contended for without the row lock or ITL waits. AN example of the output of the report, edited for length, is shown below. Object ITL Waits Busy Waits Row Lock Waits Physical Reads Logical Reads ------------- --------- ----------------- -------------- -------------- ------------- BILLING 0 63636 38267 1316055 410219712 BILLING_INDX1 1 16510 55 151085 21776800 ... DELIVER_INDX1 1963 36096 32962 1952600 60809744 DELIVER_INDX2 88 16250 9029 18839481 342857488 DELIVER_PK 2676 99748 29293 15256214 416206384 ... All Objects 12613 20348859 1253057 1139977207 20947864752 In the above report the BILLING_INDX1 index has a large amount of buffer busy waits but we can't account for them from the ITL or Row lock waits, this indicates that the index is being constantly read and the blocks then aged out of memory forcing waits as they are re-read for the next process. On the other hand, almost all of the buffer busy waits for the DELIVER_INDX1 index can be attributed to ITL and Row Lock waits. In situations where there are large numbers of ITL waits we need to consider the increase of the INITRANS setting for the table to remove this source of contention. ORA-00060 and INITRANS shortage There can also be a ORA-00060 "deadlock detected" where the table and index INITRANS is set too low. The "Interested Transaction List" and deadlocks caused by an ITL-shortage as described in MOSC note 62354.1. The eBook "Oracle Space Management Handbook" also has notes on the internals of ITL. You can also see ITL waits in a STATSPACK or AWR report, in the segments section we see this section: Segments by ITL Waits: Includes segments that had a large contention for Interested Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage parameter of the table. Also, Arup Nanda shares scripts for detecting ITL waits: Select s.sid SID, s.serial# Serial#, l.type type, ' ' object_name, lmode held, request request from v$lock l, v$session s, v$process p where s.sid = l.sid and s.username <> ' ' and s.paddr = p.addr and l.type <> 'TM' and (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6) union select s.sid SID, s.serial# Serial#, l.type type, object_name object_name, lmode held, request request from v$lock l, v$session s, v$process p, sys.dba_objects o where s.sid = l.sid and o.object_id = l.id1 and l.type = 'TM' and s.username <> ' ' and s.paddr = p.addr union select s.sid SID, s.serial# Serial#, l.type type, '(Rollback='||rtrim(r.name)||')' object_name, lmode held, request request from v$lock l, v$session s, v$process p, v$rollname r where s.sid = l.sid and l.type = 'TX' and l.lmode = 6 and trunc(l.id1/65536) = r.usn and s.username <> ' ' and s.paddr = p.addr order by 5, 6;

Waits due to Insufficient 'ITL' slots in a Block Oracle keeps note of which rows are locked by which transaction in an area at the top of each data block known as the 'interested transaction list'. The number of ITL slots in any block in an object is controlled by the INITRANS and MAXTRANS attributes. INITRANS is the number of slots initially created in a block when it is first used, while MAXTRANS places an upper bound on the number of entries allowed. Each transaction which wants to modify a block requires a slot in this 'ITL' list in the block. MAXTRANS places an upper bound on the number of concurrent transactions which can be active at any single point in time within a block. INITRANS provides a minimum guaranteed 'per-block' concurrency. If more than INITRANS but less than MAXTRANS transactions want to be active concurrently within the same block then the ITL list will be extended BUT ONLY IF THERE IS SPACE AVAILABLE TO DO SO WITHIN THE BLOCK. If there is no free 'ITL' then the requesting session will wait on one of the active transaction locks in mode 4. --Ses#1: UPDATE tx_eg SET txt='Garbage' WHERE num=1; --Ses#2: UPDATE tx_eg SET txt='Different' WHERE num=2; --DBA: SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX'; SID TY ID1 ID2 LMODE REQUEST ---------- -- ---------- ---------- ---------- ---------- 8 TX 327688 48 6 0 10 TX 327688 48 0 4 This shows SID 10 is waiting for the TX lock held by SID 8 and it wants the lock in share mode (as REQUEST=4). --Ses#1: COMMIT; --Ses#2: COMMIT; --Ses#1: ALTER TABLE tx_eg MAXTRANS 2; --Ses#1: UPDATE tx_eg SET txt='First' WHERE num=1; --Ses#2: UPDATE tx_eg SET txt='Second' WHERE num=2; Both rows update as there is space to grow the ITL list to accommodate both transactions. --Ses#1: COMMIT; --Ses#2: COMMIT; You can check the ITL Waits in v$segment_statistics with a query like: SELECT t.owner, t.object_name, t.object_type, t.statistic_name, t.value FROM v$segment_statistics t WHERE t.statistic_name = 'ITL waits' AND t.value > 0; In earlier releases of Oracle Database, the MAXTRANS parameter limited the number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated in 10g and higher. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block. ITL contention is likely to generate waits for the 'enq: TX - allocate ITL entry' wait event. If need be, increase INITTRANS and MAXTRANS to resolve this. There is more on how to troubleshoot these waits in the following article:

The Arup Nanda Blog Confessions of an Oracle Database Junkie - Arup Nanda The opinions expressed here are mine and mine alone. They may not necessarily reflect that of my employers and customers - both past or present. The comments left by the reviewers are theirs alone and may not reflect my opinion whether implied or not. None of the advice is warranted to be free of errors and ommision. Please use at your own risk and after thorough testing in your environment. Home Collection of Some of My Very Popular Published Web Articles Wednesday, January 19, 2011 More on Interested Transaction Lists How Oracle Locking Works When a Lock is NOT a Lock! In the last installment of this series "100 Thing you Probably Didn't Know About Oracle" you learned how Oracle locks the rows of a table. Here is what you learned in a nutshell: (1) When a transaction modifies a record, the pre-change image is stored in the undo segments, which is required for various things; the most important of which is to provide a read consistent version of the row when another session wants it. (2) The transaction is assigned a transaction identifier that shows the undo segment number, slot# and record of the undo information. (3) The transaction locks the rows (since it did not commit) by placing a special type of data in the block header known as Interested Transaction List (ITL) entry. The ITL entry shows the transaction ID and other information. (4) When a new transaction wants to update the same rows (locked by the previous transaction) it checks the ITL entries in the block first, to check if there is a lock. (5) Since the lock information of rows is stored in the block itself, and the ITL entries in the block refer to the locks on the rows in that block alone, there is no need to have a central lock manager to dispense and handle the release of the locks. This makes the locking process not only immensely scalable but feasible as well since there is no theoretical limit to the number of locks. [Updated Jan 22, 2011] [Thank you, Randolph Geist (info@www.sqltools-plusplus.org) for pointing it out. I follow his blog http://oracle-randolf.blogspot.com/, which is a treasure trove of information. (6) The information that a row is locked is stored along with the row in the form of a lock byte. [End of Update Jan 22, 2011] While the article might have answered some of the vexing questions you may have had or needed some clarity on the concepts you were somewhat familiar with, I sincerely hope it has piqued you curiosity to learn even more about these concepts. If I was successful in explanation, now you should not be satisfied, you should have more questions. If you don’t have any, then I completely failed in my explanation. So, what are the questions? For starters, how do you know what objects being locked in the transaction? It’s actually quite trivial. The view V$LOCK has provided that information for years, albeit in a convoluted form. A new view V$LOCKED_OBJECT is a bit more user-friendly. Let’s examine that with an example. First, update a row: SQL> update itltest set col2 = 'CHANGED BY SESSION AGAIN' where col1 = 221 2 / 1 row updated. We can check the transaction ID: SQL> select dbms_transaction.local_transaction_id from dual' LOCAL_TRANSACTION_ID -------------------------------------------------------------------------------- 2.16.41316 1 row selected. As you learned from the previous installment in this series, the transaction ID is a series of numbers denoting undo segment number, slot# and record# (also known as sequence#) respectively, separated by periods. Now, check the view V$LOCKED_OBJECT: SQL> select * from v$locked_object 2 / XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ---------- ---------- ---------- ---------- ---------- ORACLE_USERNAME OS_USER_NAME ------------------------------ ------------------------------ PROCESS LOCKED_MODE ------------------------ ----------- 2 16 41316 95263 56 ARUP oracle 13181 3 The view shows Undo Segment# (XIDUSN), Undo Slot# (XIDSLOT) and Undo Rec# (XIDSQN), which can be used to construct the transaction ID to be joined with the V$TRANSACTION to get the details. The view contains the column OBJECT_ID. Another important column is LOCKED_MODE, which shows the mode the rows are locked. In this case, it’s “3”, which means Row Exclusive. Here is a script that decodes the modes as well as reports the object name. select owner object_owner, object_name object_name, session_id oracle_sid, oracle_username db_user, decode(LOCKED_MODE, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Sub Share Exclusive', 6, 'Exclusive', locked_mode ) locked_mode from v$locked_object lo, dba_objects do where (xidusn||'.'||xidslot||'.'||xidsqn) = ('&transid') and do.object_id = lo.object_id / Save this script and execute it when you need further details on the transaction. The script will ask for the transaction ID which you can pass in the format reported by dbms_transaction.local_transaction_id. Next, you may draw my attention to the point #3 above. If there are 10 records in the block and a transaction updated (and therefore locked) all ten of them, how many ITL entries will be used – one or ten? Good question (I have to say that, since I asked that :) I suppose you can answer that yourself. Ten ITL slots may be feasible; but what if the block has 10,000 records? Is it possible to have that many ITL slots in the block header? Let’s ponder on that for a second. There will be two big issues with that many ITL slots. First, each ITL slot, by the way, is 24 bytes long. So, 10000 slots will take up 240,000 bytes or almost 22 KB. A typical Oracle block is 8KB (I know, it could be 2K, 4K or 16K; but suppose it is the default 8K). Of course it can’t accommodate 22KB. Second, even if the total size of the ITL slots is less than the size of the block, where will be the room to hold data? In addition, there should be some space for the data block overhead; where will that space come from? Obviously, these are genuine problems that make one ITL slot per row impractical. Therefore Oracle does not create an ITL entry for each locked row. Instead, it creates the ITL entry for each transaction, which may have updated a number of rows. Let me repeat that – each ITL slot in the block header actually refers to a transaction; not the individual rows. That is the reason why you will not find the rowid of the rows locked in the ITL slot. Here is the ITL entry from the block header, again: Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.019.00007c05 0x00c00288.1607.0e ---- 1 fsc 0x0000.00000000 0x02 0x0003.017.00009e24 0x00c00862.190a.0f C--- 0 scn 0x0000.02234e2b There is a reference to a transaction ID; but not rowid. When a transaction wants to update a row in the block, it checks the ITL entries. If there is none, it means rows in that block are unlocked. However, if there are some ITL entries, does it mean that some rows in the block are locked? Not necessarily. It simply means that the rows the block were locked earlier; but that lock may or may not be active now. To check if a row is locked, the transaction checks for the lock byte stored along with the row. That brings up an interesting question. If presence of an ITL slot does not mean a record in the block is locked, when does the ITL slot get cleared so that it can be reused, or when does that ITL slot disappear? Shouldn’t that ITL slot disappear when the transaction ends by commit or rollback? That should be the next burning question throbbing in your head right now. Clearing of ITL Slots To answer that question, consider this scenario: a transaction updates 10000 records, on 10000 different blocks. Naturally there will be 10000 ITL slots, one on each block, all pointing to the same transaction ID. The transaction commits; and the locks are released. Should Oracle revisit each block and remove the ITL entry corresponding to the transaction as a part of the commit operation? If that were the processing logic, the commit would have taken a very long time. Acquiring the buffers of the 10000 blocks and updating the ITL entry will not be quick; it will take a very long time, prolonging the commit processing. From part 1 of the series, you learned that the commit processing is actually very quick, with a flush of the log buffer to redo logs and the writing of the commit marker in the redo stream. Even a checkpoint to the datafiles is not done as a part of commit processing – all the effort going towards making the process fast, very fast. Had Oracle added the logic of altering ITL slots, the commit processing would have been potentially long, very long. Therefore Oracle does not remove the ITL entries after that transaction ends (by committing, or rolling back); the slots are just left behind as artifacts. The proof, as they say, is in the pudding. Let’s see with an example: SQL> create table itltest (col1 number, col2 varchar2(200)); Table created. SQL> begin 2 for i in 1..1000 loop 3 insert into itltest values ( 4 i,'INITIAL VALUE OF COLUMN'); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> commit; Commit complete. This inserts 1000 records. Let’s find out the file and block these records go to: 1 select 2 dbms_rowid.rowid_relative_fno(rowid) File#, 3 dbms_rowid.rowid_block_number(rowid) Block#, 4 count(1) 5 from itltest 6 group by 7 dbms_rowid.rowid_relative_fno(rowid), 8 dbms_rowid.rowid_block_number(rowid) 9 order by 10* 1,2 SQL> / FILE# BLOCK# COUNT(1) ---------- ---------- ---------- 7 4027 117 7 4028 223 7 4029 220 7 4030 220 7 4031 220 5 rows selected. Let’s identify the rows in a specific block, block# 4028, for instance. SQL> select min(col1), max(col1) 2 from itltest 3 where dbms_rowid.rowid_block_number(rowid) = 4028 SQL> / MIN(COL1) MAX(COL1) ---------- ---------- 1 223 1 row selected. Block 4028 has the rows 1 through 223. That’s all we need to know for now. We will limit our activity to this block alone. We will need to update a single row in this block from a session: SQL> update itltest set col2 = ‘Changed’ where col1 = 1; Do NOT commit; just keep the session at this point. Open a different session, and update a different row, e.g. one with col1 = 2. Since this is a different row, there will be no lock contention. Similarly update 20 other rows on this block. There will be 20 different transactions on the rows of this table. Let’s examine the innards of the block by dumping it. Before that, we should flush the block to the disk. SQL> alter system checkpoint; System altered. SQL> alter system dump datafile 7 block min 4028 block max 4028; System altered. The information will be written to a tracefile. We have to know the SPID of the process to identify the tracefile: SQL> select p.spid 2 from v$session s, v$process p 3 where s.sid = (select sid from v$mystat where rownum < 2) 4* and p.addr = s.paddr SQL> / SPID ------------------------ 9537 We will locate a file called D112D2_ora_9537.trc in the trace directory. Please note, this tracefile is named OracleSID_ora_ProcessID.trc; so the exact name will be different your system. Open the file and search for “Itl”. Here is an excerpt from the file: Block header dump: 0x01c00fbc Object id on Block? Y seg/obj: 0x1741f csc: 0x00.235a849 itc: 36 flg: E typ: 1 - DATA brn: 0 bdba: 0x1c00fb8 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0008.00d.0000a1eb 0x00c015d1.1d3c.28 ---- 1 fsc 0x0005.00000000 0x02 0x0007.018.00007fab 0x00c01246.180b.21 ---- 1 fsc 0x0005.00000000 0x03 0x0003.004.0000a1b0 0x00c005ef.1a18.07 ---- 1 fsc 0x0005.00000000 0x04 0x0010.010.00000004 0x00c011ee.0001.10 ---- 1 fsc 0x0005.00000000 0x05 0x000e.00e.00000003 0x00c011cb.0001.0f ---- 1 fsc 0x0005.00000000 0x06 0x000c.00e.00000003 0x00c011ab.0001.1b ---- 1 fsc 0x0005.00000000 0x07 0x0013.011.00000004 0x00c00f9c.0001.0f ---- 1 fsc 0x0005.00000000 0x08 0x0002.00a.0000a166 0x00c014d8.1c06.12 ---- 1 fsc 0x0005.00000000 0x09 0x0001.010.00007f65 0x00c00cd3.16ae.14 ---- 1 fsc 0x0005.00000000 0x0a 0x0014.01b.00000008 0x00c00faa.0003.67 ---- 1 fsc 0x0005.00000000 0x0b 0x000f.00f.00000003 0x00c011db.0001.20 ---- 1 fsc 0x0005.00000000 0x0c 0x000d.00f.00000004 0x00c011bb.0001.1d ---- 1 fsc 0x0005.00000000 0x0d 0x0012.010.00000003 0x00c00f8b.0001.1c ---- 1 fsc 0x0005.00000000 0x0e 0x000a.00c.00007f76 0x00c003d7.16ea.31 ---- 1 fsc 0x0005.00000000 0x0f 0x0011.010.00000004 0x00c011fb.0001.10 ---- 1 fsc 0x0005.00000000 0x10 0x0009.000.0000a236 0x00c00e91.1bbe.17 ---- 1 fsc 0x0005.00000000 0x11 0x0006.00e.0000a1fc 0x00c0035c.1c24.2d ---- 1 fsc 0x0005.00000000 0x12 0x000b.012.00000003 0x00c01193.0001.1d ---- 1 fsc 0x0005.00000000 0x13 0x0004.00e.00007ff7 0x00c00d01.1771.0a ---- 1 fsc 0x0005.00000000 0x14 0x0005.002.0000a19f 0x00c00f1a.1bd6.1d ---- 1 fsc 0x0005.00000000 0x15 0x0015.000.00000002 0x00c00fba.0000.02 ---- 1 fsc 0x0005.00000000 0x16 0x0016.000.00000002 0x00c00fca.0000.02 ---- 1 fsc 0x0005.00000000 0x17 0x0017.000.00000002 0x00c00fda.0000.02 ---- 1 fsc 0x0005.00000000 0x18 0x0018.000.00000002 0x00c00fea.0000.02 ---- 1 fsc 0x0005.00000000 0x19 0x0019.000.00000002 0x00c00ffa.0000.02 ---- 1 fsc 0x0005.00000000 0x1a 0x001a.000.00000002 0x00c0100a.0000.02 ---- 1 fsc 0x0005.00000000 0x1b 0x001b.000.00000002 0x00c0101a.0000.02 ---- 1 fsc 0x0005.00000000 0x1c 0x001c.000.00000002 0x00c0102a.0000.02 ---- 1 fsc 0x0005.00000000 0x1d 0x001d.000.00000002 0x00c0103a.0000.02 ---- 1 fsc 0x0005.00000000 0x1e 0x001e.002.00000002 0x00c0104a.0000.03 ---- 1 fsc 0x0005.00000000 0x1f 0x001f.002.00000002 0x00c0105a.0000.03 ---- 1 fsc 0x0005.00000000 0x20 0x0020.000.00000002 0x00c0106a.0000.02 ---- 1 fsc 0x0005.00000000 0x21 0x0021.005.00000002 0x00c0107a.0000.08 ---- 1 fsc 0x0000.00000000 0x22 0x0022.000.00000002 0x00c0108a.0000.02 ---- 1 fsc 0x0005.00000000 0x23 0x0023.000.00000002 0x00c0109a.0000.02 ---- 1 fsc 0x0005.00000000 0x24 0x0024.000.00000002 0x00c010aa.0000.02 ---- 1 fsc 0x0005.00000000 bdba: 0x01c00fbc data_block_dump,data header at 0xeb6994 Note the Itl entries – there is an entry for each transaction, marked by its transaction ID, as expected. When the block was created, there were two ITL slots. As the demand for locks increased, additional slots were created and used for these new transactions. Now go to all these sessions and either commit or rollback to end the transactions. Dump the block and search for “Itl”. The ITL slots are still there, even though the transactions have ended and the locks released. Oracle does not update the ITL entries. So, when does the ITL entry gets cleared? When block’s buffer is written to the disk, the unneeded ITL entries are checked and cleared out. Let’s force a block flushing: SQL> alter system checkpoint; Now dump the data block once again and examine the ITLs. Here is an excerpt from the tracefiles. Object id on Block? Y seg/obj: 0x1741f csc: 0x00.235a849 itc: 36 flg: E typ: 1 - DATA brn: 0 bdba: 0x1c00fb8 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0014.016.00000008 0x00c00fb3.0002.11 C--- 0 scn 0x0000.0235a524 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x0b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x0c 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x0d 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x0e 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x0f 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x10 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x11 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x12 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x13 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x14 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x15 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x16 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x17 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x18 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x19 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x1a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x1b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x1c 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x1d 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x1e 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x1f 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x20 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x21 0x0021.002.00000002 0x00c0107a.0000.05 C--- 0 scn 0x0000.0235a807 0x22 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x23 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x24 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x01c00fbc data_block_dump,data header at 0x484994 Note the Xid columns – the transaction Id, which shows 0’s, meaning there is no transaction using the ITL slots. These ITL slots are eligible for reuse. Update two rows from two different sessions, checkpoint and dump the block once again. Here is the ITL information again: Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0014.016.00000008 0x00c00fb3.0002.11 C--- 0 scn 0x0000.0235a524 0x02 0x0005.009.0000a1a5 0x00c00f21.1bd6.04 ---- 1 fsc 0x0016.00000000 0x03 0x000a.002.00007f7a 0x00c003d8.16ea.13 ---- 1 fsc 0x0016.00000000 0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ... and so on ... The first two Itl slots are now used. Note, only the ITL slots in this specific block will be created. All other blocks will continue to have the same number of ITL slots. ITL Waits From the part 2 of this series you learned that the ITL slots are not preallocated, at least not all of them. When a transaction needs to lock rows in the block, and it does not find an unused ITL slot, Oracle creates a new ITL slot for the transaction. Consider the figure below. There is no more room in the block for a new ITL entry. A new transaction comes in to update Record3. What will happen? The transaction will have to wait. This is not the same wait as a row lock; because there is no lock on the row marked Record3.Instead, session will wait on a special wait event. You can check the wait event from the V$SESSION view. SQL> select event 2 from v$session 3 where sid = 78 4 / EVENT ---------------------------------------------------------------- enq: TX - allocate ITL entry The moment one of the transactions – from either Session1 or Session2 end by commit or rollback, the new transaction can grab that ITL slot and complete the locking operation. You will see that wait event disappear. Since the ITL waits come and go, how do you capture them; or more specifically how will you know which objects are being subjected to this wait? It’s fairly trivial. Since Oracle 9.2 a new view – V$SEGMENT_STATSTICS – shows various segment related statistics on segments. Here is an example: SQL> select statistic_name, value from v$segment_statistics 2* where object_name = 'ITLTEST'; STATISTIC_NAME VALUE ---------------------------------------------------------------- ---------- logical reads 7216 buffer busy waits 3 gc buffer busy 0 db block changes 5600 physical reads 0 physical writes 39 physical read requests 0 physical write requests 9 physical reads direct 0 physical writes direct 0 optimized physical reads 0 gc cr blocks received 0 gc current blocks received 0 ITL waits 2 row lock waits 1 Various stats on the segment named ITLTEST are listed here. Of the lot, the one interesting to our discussion here is “ITL waits”, which shows “2”. It means the table ITLTEST has waited 2 times for ITL waits (not for a legitimate row locking, which shown in the stats immediately afterwards). Conversely, you may want to find out what have been subjected to ITL waits. The following query shows you that: SQL> select owner, object_name 2 from v$segment_statistics 3 where statistic_name = 'ITL waits' 4* and value > 0; OWNER OBJECT_NAME ------------------------------ ------------------------------ ARUP ITLTEST 1 row selected. The view has many more columns for making filtering easier: SQL> desc v$segment_statistics Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) TS# NUMBER OBJ# NUMBER DATAOBJ# NUMBER OBJECT_TYPE VARCHAR2(18) STATISTIC_NAME VARCHAR2(64) STATISTIC# NUMBER VALUE NUMBER Actually selecting from the above view is a bit expensive on the database. The base view is V$SEGSTAT, shown below: SQL> desc v$segstat Name Null? Type ----------------------------------------- -------- ---------------------------- TS# NUMBER OBJ# NUMBER DATAOBJ# NUMBER STATISTIC_NAME VARCHAR2(64) STATISTIC# NUMBER VALUE NUMBER While V$SEGMENT_STATISTICS show much more information, it’s a little slow due to all those joins. If you don’t need all that information, you may want to select instead from V$SEGSTAT, which is usually faster. The columns are self explanatory; but here they are in any case: TS# - the tablespace number. You can use this to get the tablespace name from TS$ table joined by TS# column OBJ# - the object_id, from dba_objects. You can get the rest of the details from that view DATAOBJ# - the data_object_id, from dba_objects. This is usually the same as object_id; except in case of sub-objects such as partitions in which case they differ. One important point about this view: like all V$ views, it shows information from the start of the instance. When the instance recycles, the values are reset to 0. To get a historical information, you should periodically select from this view and store in a regular table. If you have AWR enabled, you can check the historical records from there. Here is an example: SQL> select snap_id, ITL_WAITS_TOTAL, ITL_WAITS_DELTA 2 from DBA_HIST_SEG_STAT 3 where obj# = 95263 4* order by snap_id; SNAP_ID ITL_WAITS_TOTAL ITL_WAITS_DELTA ---------- --------------- --------------- 5014 2 2 1 row selected. Solution Well, so far I talked about a problem. Is there a solution? Of course there is. Remember, the cause of ITL waits is simply space inside a block. If there is no space inside the block to grow the ITL list to add more slots, the sessions will wait with the ITL wait event. So the solution is to reserve same space for that growth. There are two basic alternatives to solve the ITL wait problem: (1) INITRANS. Remember the little clause during table or index creation? Have you ever explicitly set it to its non-default value? Most likely you haven’t. It specifies the number of ITL slots that must be initially created on a block. If you specify 10, then 10 ITL slots are created on the block, guaranteeing the slot for 10 transactions. The 11th transaction will need to extend the ITL list; or wait if that is not possible. To check for the INITRANS value of tables, use: SQL> select ini_trans 2 from dba_tables 3 where table_name = 'T'; INI_TRANS ---------- 10 (2) Less Space for Data The other option is to make sure that you have less data inside a data block to allow the ITL sufficient free space. You can do it by several ways – by setting a high value of PCTFREE and by setting MINIMIZE_RECORDS_PER_BLOCK clause. Obviously, both these options waste space inside the block; so you should use these only on those segments that experience high ITL waits, as you can see from AWR reports or your homegrown data collectors. To increase the INITRANS of an existing table, you should issue: ALTER TABLE ITLTEST INITRANS 10; Remember, the setting affects the new blocks only; not the existing ones. You can issue ALTER TABLE … MOVE command to relocate the blocks to new blocks, and thereby effecting the new settings. What is the upper limit of the ITL slots? They are set by a parameter of the object called MAXTRANS. The default is 256. If you set it to 20, the ITL slots will go up to that much only. However, the parameter has no effect in Oracle 10gR2. It’s ignored and the ITL slots can go up to 256. Takeaways In this installment you learned: (1) ITL itself does not say whether a row is locked or not. The lock byte stored in the row tells that. (2) When a transaction ends, the corresponding ITL entry is not removed or altered. It gets cleared during flush to the disk. (3) When the ITL can’t grow due to the lack of space in the block, the session waits will the event “enq: TX - allocate ITL entry” (4) You can identify the segments that have suffered from this wait by checking the view V$SEGSTAT. (5) To reduce the possibility of these waits, you should have sufficient space inside the data block for ITL expansion, either by defining higher number of initial ITL slots, or forcing less data inside the blocks.

ITL ITIL